﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;

namespace CCPSIP
{
    public class OracleKitVCard
    {
        private const string kOracleServer = "ISENDER.CCP.COM.TW";
        private const string kOracleUserId = "ISENDER";
        private static byte[] kOracleKey = { 0x61, 0x4B, 0x68, 0x4A, 0x4A, 0x46, 0x41, 0x32, 0x73, 0x57, 0x66, 0x45, 0x73, 0x51, 0x7A, 0x30, 0x5A, 0x6D, 0x35, 0x69, 0x77, 0x51, 0x69, 0x39, 0x67, 0x6A, 0x69, 0x56, 0x4E, 0x44 };

        private static string GetConnStrMIS()
        {
            return String.Format(
              "Data Source={0};Persist Security Info=True;User ID={1};Password={2}",
              kOracleServer, kOracleUserId, System.Text.Encoding.UTF8.GetString(kOracleKey));
        }

        /// <summary>
        /// 儲存 vCard 交換請求
        /// </summary>
        /// <param name="Scanner">掃描者的SIP帳號</param>
        /// <param name="Replier">vCard回覆方(QR Code持有人)</param>
        /// <param name="InitTime">Web Service呼叫時間</param>
        public static void NewVCardSend(string Scanner, string Replier, DateTime InitTime)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();
                myTrans = connOracle.BeginTransaction();

                //【01】取消重覆的請求
                //
                string updStr = "UPDATE vcard_send SET Status='C'"
                  + " WHERE Status='0'"
                  + " AND Scanner=:Scanner AND Replier=:Replier";
                OracleCommand updCmd = new OracleCommand(updStr, connOracle, myTrans);
                updCmd.Parameters.Add("Scanner", OracleType.VarChar).Value = Scanner;
                updCmd.Parameters.Add("Replier", OracleType.VarChar).Value = Replier;
                updCmd.ExecuteNonQuery();

                //【02】儲存 vCard 交換請求
                //
                string insStr = "INSERT INTO vcard_send"
                  + " (InitTime, Status, Scanner, Replier, Pushed) VALUES"
                  + " (:InitTime, :Status, :Scanner, :Replier, :Pushed)";
                OracleCommand insCmd = new OracleCommand(insStr, connOracle, myTrans);
                insCmd.Parameters.Add("InitTime", OracleType.DateTime).Value = InitTime;
                insCmd.Parameters.Add("Status", OracleType.VarChar).Value = "0";
                insCmd.Parameters.Add("Scanner", OracleType.VarChar).Value = Scanner;
                insCmd.Parameters.Add("Replier", OracleType.VarChar).Value = Replier;
                insCmd.Parameters.Add("Pushed", OracleType.VarChar).Value = "N";
                insCmd.ExecuteNonQuery();

                //【99】Commit Transaction
                //
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得待確認的vCardExchInfo Array
        /// </summary>
        /// <param name="Replier">vCard回覆方(QR Code持有人)</param>
        /// <returns>待確認的vCardExchInfo Array</returns>
        public static vCardExchInfo[] getReqList(string Replier)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT pid, InitTime, Scanner, Replier, ReplyTime"
                  + " FROM vcard_send"
                  + " WHERE Status='0' AND Replier=:Replier"
                  + " ORDER BY InitTime DESC, pid DESC";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("Replier", OracleType.VarChar).Value = Replier;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                List<vCardExchInfo> resultList = new List<vCardExchInfo>();
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    resultList.Add(new vCardExchInfo(row));
                }
                return resultList.ToArray();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 回覆 vCard 交換請求(接受或拒絕)
        /// </summary>
        /// <param name="userInfo">vCard回覆方的SipUserInfo物件</param>
        /// <param name="Scanner">掃描者的SIP帳號</param>
        /// <param name="ReplyTime">Web Service呼叫時間</param>
        /// <param name="Accepted">接受true，否則false</param>
        public static void ReplyVCardSend(SipUserInfo userInfo, string Scanner, DateTime ReplyTime, bool Accepted)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                if (Accepted)
                {
                    //【01】設定朋友關係
                    //
                    SipUserInfo userInfo2 = SipKit.GetUserInfo(Scanner, connOracle);
                    OracleKitContact.MakeFriends(userInfo, userInfo2, "vCard", connOracle);
                }

                //【02】更新vCard交換請求狀態
                //
                string updStr = "UPDATE vcard_send"
                  + " SET Status=:Status, Pushed='N', ReplyTime=:ReplyTime"
                  + " WHERE Status='0'"
                  + " AND Scanner=:Scanner AND Replier=:Replier";
                OracleCommand updCmd = new OracleCommand(updStr, connOracle);
                updCmd.Parameters.Add("Status", OracleType.VarChar).Value = (Accepted ? "Y" : "N");
                updCmd.Parameters.Add("ReplyTime", OracleType.DateTime).Value = ReplyTime;
                updCmd.Parameters.Add("Scanner", OracleType.VarChar).Value = Scanner;
                updCmd.Parameters.Add("Replier", OracleType.VarChar).Value = userInfo.SIP_Num;
                updCmd.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得待下載的vCard清單
        /// </summary>
        /// <param name="SIP_Num">User的SIP帳號</param>
        /// <returns>待下載的vCardExchInfo Array</returns>
        public static vCardExchInfo[] getToDownloadList(string SIP_Num)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT pid, InitTime, Scanner, Replier, ReplyTime"
                  + " FROM vcard_send"
                  + " WHERE Status='Y'"
                  + "   AND ((DLTimeS IS NULL AND Scanner=:SIP_Num) OR (DLTimeR IS NULL AND Replier=:SIP_Num))"
                  + " ORDER BY ReplyTime DESC, pid DESC";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                List<vCardExchInfo> resultList = new List<vCardExchInfo>();
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    resultList.Add(new vCardExchInfo(row));
                }
                return resultList.ToArray();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得vCard交換請求資訊
        /// </summary>
        /// <param name="vCardExchID">vCard交換請求ID</param>
        /// <returns>vCardExchInfo物件</returns>
        public static vCardExchInfo getExchInfo(int vCardExchID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT pid, InitTime, Scanner, Replier, ReplyTime"
                  + " FROM vcard_send"
                  + " WHERE Status='Y' AND pid=:vCardExchID";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("vCardExchID", OracleType.Int32).Value = vCardExchID;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRow row = dataSet.Tables[0].Rows[0];
                    return new vCardExchInfo(row);
                }
            }
            finally
            {
                connOracle.Close();
            }
            return null;
        }


        /// <summary>
        /// 記錄 vCard 確認下載時間
        /// </summary>
        /// <param name="vCardOwner">vCard上傳者的帳號</param>
        /// <param name="vCardTime">vCard上傳時間</param>
        /// <param name="SIP_Num">下載者SIP帳號</param>
        /// <returns>記錄Update筆數</returns>
        public static int UpdateDLTime(string vCardOwner, DateTime vCardTime, string SIP_Num)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                vCardExchPosition? position = null;

                //【01】檢查下載者是否為掃描者
                //
                string selStr1 = "SELECT COUNT(*) FROM vcard_send"
                  + " WHERE Status='Y'"
                  + "   AND Scanner=:Scanner AND DLTimeS IS NULL"
                  + "   AND Replier=:Replier AND ReplyTime=:ReplyTime";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                selCmd1.Parameters.Add("Scanner", OracleType.VarChar).Value = SIP_Num;
                selCmd1.Parameters.Add("Replier", OracleType.VarChar).Value = vCardOwner;
                selCmd1.Parameters.Add("ReplyTime", OracleType.DateTime).Value = vCardTime;
                int count = Convert.ToInt32(selCmd1.ExecuteScalar());

                if (count > 0)
                {
                    position = vCardExchPosition.Scanner;
                }
                else
                {
                    //【02】檢查下載者是否為回覆者
                    //
                    string selStr2 = "SELECT COUNT(*) FROM vcard_send"
                      + " WHERE Status='Y'"
                      + "   AND Replier=:Replier AND DLTimeR IS NULL"
                      + "   AND Scanner=:Scanner AND InitTime=:InitTime";
                    OracleCommand selCmd2 = new OracleCommand(selStr2, connOracle);
                    selCmd2.Parameters.Add("Replier", OracleType.VarChar).Value = SIP_Num;
                    selCmd2.Parameters.Add("Scanner", OracleType.VarChar).Value = vCardOwner;
                    selCmd2.Parameters.Add("InitTime", OracleType.DateTime).Value = vCardTime;
                    count = Convert.ToInt32(selCmd2.ExecuteScalar());

                    if (count > 0)
                    {
                        position = vCardExchPosition.Replier;
                    }
                }

                //【03】記錄確認下載時間
                //
                if (position.HasValue)
                {
                    OracleCommand updCmd3;

                    if (position == vCardExchPosition.Scanner)
                    {
                        string updStr3 = "UPDATE vcard_send SET DLTimeS=:DateTime"
                          + " WHERE Status='Y'"
                          + "   AND Scanner=:Scanner AND DLTimeS IS NULL"
                          + "   AND Replier=:Replier AND ReplyTime=:ReplyTime";
                        updCmd3 = new OracleCommand(updStr3, connOracle);
                        updCmd3.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                        updCmd3.Parameters.Add("Scanner", OracleType.VarChar).Value = SIP_Num;
                        updCmd3.Parameters.Add("Replier", OracleType.VarChar).Value = vCardOwner;
                        updCmd3.Parameters.Add("ReplyTime", OracleType.DateTime).Value = vCardTime;
                    }
                    else
                    {
                        string updStr3 = "UPDATE vcard_send SET DLTimeR=:DateTime"
                          + " WHERE Status='Y'"
                          + "   AND Replier=:Replier AND DLTimeR IS NULL"
                          + "   AND Scanner=:Scanner AND InitTime=:InitTime";
                        updCmd3 = new OracleCommand(updStr3, connOracle);
                        updCmd3.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                        updCmd3.Parameters.Add("Replier", OracleType.VarChar).Value = SIP_Num;
                        updCmd3.Parameters.Add("Scanner", OracleType.VarChar).Value = vCardOwner;
                        updCmd3.Parameters.Add("InitTime", OracleType.DateTime).Value = vCardTime;
                    }
                    return updCmd3.ExecuteNonQuery();
                }

                return 0;
            }
            finally
            {
                connOracle.Close();
            }
        }


    }


    /// <summary>
    /// vCard 交換資訊
    /// </summary>
    public class vCardExchInfo
    {
        private UInt32 _vCardExchID; //交換請求ID
        private DateTime _InitTime;  //交換起始時間
        private string _Scanner;//vCard QR Code 掃描方
        private string _Replier;//vCard 交換請求回覆方
        private DateTime? _ReplyTime;//交換請求回覆時間

        public UInt32 vCardExchID { get { return this._vCardExchID; } }
        public DateTime InitTime { get { return this._InitTime; } }
        public string Scanner { get { return this._Scanner; } }
        public string Replier { get { return this._Replier; } }
        public DateTime? ReplyTime { get { return this._ReplyTime; } }


        public vCardExchInfo(DataRow row)
        {
            _vCardExchID = Convert.ToUInt32(row["pid"]);
            _InitTime = Convert.ToDateTime(row["InitTime"]);
            _Scanner = Convert.ToString(row["Scanner"]);
            _Replier = Convert.ToString(row["Replier"]);

            if (row["ReplyTime"] == DBNull.Value)
                _ReplyTime = null;
            else
                _ReplyTime = Convert.ToDateTime(row["ReplyTime"]);
        }
    }


    /// <summary>
    /// vCard 交換立場
    /// </summary>
    public enum vCardExchPosition { Scanner, Replier };
}